import os
import datetime

import sqlite3 as dbapi

import datamodel
import tvweb

class TvDB(object):
    
    dire = ''
    __create = True

    def __init__(self):
        self.tv = tvweb.TvWeb()
        if not os.path.isdir(TvDB.dire):
            os.makedirs(TvDB.dire)
        self.__db = dbapi.connect(TvDB.dire + "pytv.dat")
        self.__cursor = self.__db.cursor()
        if TvDB.__create:
            self._create_tables()
            TvDB.__create = False
            
    def _create_tables(self):
        try:
            self.__cursor.execute('create table serie(id INTEGER PRIMARY KEY, name)')
            self.__db.commit()
        except dbapi.OperationalError, reason:
            print reason
        try:
            self.__cursor.execute('create table calendar(id INTEGER PRIMARY KEY, serie, episode, season, date, nroEp)')
            self.__db.commit()
        except dbapi.OperationalError, reason:
            print reason
        try:
            self.__cursor.execute('create table configuration(season, onDay, every, download, direct, images, hosting, formats)')
            self.__db.commit()
            self.__cursor.execute('insert into configuration values(1, 1, 6, 0, 0, 1, 0, "All")')
            self.__db.commit()
        except dbapi.OperationalError, reason:
            print reason
        try:
            self.__cursor.execute('create table plugins(name, active)')
            self.__db.commit()
        except dbapi.OperationalError, reason:
            print reason

    def store_series(self, serie):
        s = datamodel.Serie(serie)
        query = str('insert into serie values('+s.data()+')')
        self.__cursor.execute(query)
        self.tv.obtain_episodes_list(str(s))
        self.__db.commit()

    def restore_series(self):
        self.__cursor.execute('select * from serie order by name ASC')
        seriesList = [datamodel.Serie(data[1], data[0]) for data in self.__cursor]
        return seriesList

    def delete_series(self, serie):
        query = 'delete from serie where name = "' + serie + '"'
        self.__cursor.execute(query)
        self.__db.commit()
        query = 'delete from calendar where serie = "' + serie + '"'
        self.__cursor.execute(query)
        self.__db.commit()
        try:
            os.remove(TvDB.images + serie.name.replace(' ', ','))
        except:
            print 'There is no image for this serie'

    def store_calendar(self, calendar):
        for c in calendar:
            if(c.id == None):
                query = str('insert into calendar values('+c.data()+')')
                self.__cursor.execute(query)
        self.__db.commit()
        print 'Calendar Saved'

    def delete_calendar(self, serie):
        query = 'delete from calendar where serie = "' + serie + '"'
        self.__cursor.execute(query)
        self.__db.commit()

    def restore_calendar(self, serie):
        self.__cursor.execute('select * from calendar where serie = "' + serie + '" order by nroEp DESC')
        return self.complete_calendar_list()

    def restore_calendar_for_date(self, date):
        self.__cursor.execute('select * from calendar where date = "' + str(date) + '"')
        return self.complete_calendar_list()

    def restore_calendar_for_month(self, year, month):
        m = '0'+str(month) if month < 10 else str(month)
        query = 'select * from calendar where date like "'+str(year)+'-'+m+'-__"'
        self.__cursor.execute(query)
        return self.complete_calendar_list()

    def check_new_episode(self, day):
        date = datetime.date.today()
        dif = datetime.timedelta(days=day)
        date = date - dif
        query = 'select * from calendar where date = "' + str(date) + '"'
        self.__cursor.execute(query)
        return self.complete_calendar_list()

    def check_for_new_info(self):
        query = 'select name from serie where name not in (select serie from calendar group by serie) '\
                'or name in (select serie from calendar where episode = "TBA" group by serie)'
        self.__cursor.execute(query)
        seriesList = [data[0] for data in self.__cursor]
        query = 'select * from calendar'
        if len(seriesList) > 0:
            listOfSeries = ['"'+s+'"' for s in seriesList]
            query += ' where serie not in ('+ ','.join(listOfSeries) +')'
        query += ' order by serie, date DESC'
        self.__cursor.execute(query)
        calendarList = self.complete_calendar_list()
        self.tv.check_for_new_info(seriesList, calendarList, self)

    def delete_old_episodes(self):
        d = datetime.date.today()
        month = d.month - 6
        m = '0'+str(month) if month < 10 else str(month)
        query = 'delete from calendar where date like "____-'+m+'-__"'
        self.__cursor.execute(query)
        self.__db.commit()
        
    def complete_calendar_list(self):
        calendarList = [datamodel.Calendar(data[1], data[2], data[3], data[5], data[4], data[0]) for data in self.__cursor]
        return calendarList

    def search_next_episode(self, serie, date):
        query = 'select * from calendar where serie = "' + serie + '"'
        self.__cursor.execute(query)
        calendarList = self.complete_calendar_list()
        cal = [c for c in calendarList if c.date > date]
        return cal[0]

    def save_configuration(self, config):
        query = 'delete from configuration'
        self.__cursor.execute(query)
        self.__db.commit()
        query = str('insert into configuration values('+ config +')')
        self.__cursor.execute(query)
        self.__db.commit()

    def read_configuration(self):
        query = 'select * from configuration'
        self.__cursor.execute(query)
        data = [d for d in self.__cursor]
        return data[0]
        
    def save_plugin(self, name):
        query = str('insert into plugins values("'+ name +'", 0)')
        self.__cursor.execute(query)
        self.__db.commit()
        
    def restore_plugins(self):
        query = 'select * from plugins'
        self.__cursor.execute(query)
        plug = [p for p in self.__cursor]
        return plug
        
    def update_plugins(self, plugins):
        for plug in plugins:
            query = 'update plugins set active=' + str(plug[1]) + ' where name="' + plug[0] + '"'
            self.__cursor.execute(query)
        self.__db.commit()
